package org.example.dao.impl;

import org.example.dao.EmployeeDao;
import org.example.entity.Employee;
import org.example.entity.EmployeeDTO;

import java.sql.*;
import java.util.ArrayList; // 添加 ArrayList 导入
import java.util.List;

/**
 * 员工的数据访问层代码
 */
public class EmployeeDaoImpl implements EmployeeDao {
    public List<EmployeeDTO> getEmployeDTOs() {
        String sql = "select e.*,d.* from emp e inner join dept d " +
                "on e.dept_id = d.dept_id";

        return DBUtil.executeQuery(EmployeeDTO::map, sql);
    }

    public int updateEmployeeDeptId(int empId, int deptId) {
        String sql = "UPDATE emp SET dept_id=? WHERE emp_id=?";
        return DBUtil.executeSql(sql, deptId, empId);
    }

    @Override
    public void addEmp(Employee employee) {
        String sql = "insert into emp (emp_name,job,mgr_id,hire_date,sal,comm,dept_id) values (?,?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.getEmpName());
            pstmt.setString(2, employee.getJob());
            pstmt.setInt(3, employee.getMgrId());
            pstmt.setDate(4, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setDouble(5, employee.getSal());
            pstmt.setDouble(6, employee.getComm());
            pstmt.setInt(7, employee.getDept_id());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void addEmp(Connection conn, Employee employee) {
        String sql = "insert into emp (emp_name,job,mgr_id,hire_date,sal,comm,dept_id) values (?,?,?,?,?,?,?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.getEmpName());
            pstmt.setString(2, employee.getJob());
            pstmt.setInt(3, employee.getMgrId());
            pstmt.setDate(4, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setDouble(5, employee.getSal());
            pstmt.setDouble(6, employee.getComm());
            pstmt.setInt(7, employee.getDept_id());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("添加员工失败", e);
        }
    }

    @Override
    public EmployeeDTO getEmpByName(String empName) {
        String sql = "select e.*,d.* from emp e inner join dept d on e.dept_id = d.dept_id where emp_name = ?";
        List<EmployeeDTO> result = DBUtil.executeQuery(EmployeeDTO::map, sql, empName);

        if (result.isEmpty()) {
            return null;
        }

        return result.get(0);
    }

    @Override
    public List<Employee> getAllEmployees() {
        List<Employee> employees = new ArrayList<>(); // 使用 ArrayList 实现
        String sql = "SELECT emp_id,emp_name,job,mgr_id,hire_date,sal,comm,dept_id FROM emp";



        try (Connection conn = DBUtil.getConn();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Employee emp = new Employee();
                emp.setEmpId(rs.getInt("emp_id"));
                emp.setEmpName(rs.getString("emp_name"));
                emp.setJob(rs.getString("job"));
                emp.setHireDate(rs.getDate("hire_date"));
                emp.setSal(rs.getDouble("sal"));
                emp.setComm(rs.getDouble("comm"));
               emp.setDept_id(rs.getInt("dept_id"));
                employees.add(emp);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询员工列表失败", e);
        }
        return employees;
    }

    @Override
    public void deleteEmp(int empId) {
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false); // 开启事务

            // 先将该员工下属的 mgr_id 设置为 NULL
            String updateSql = "UPDATE emp SET mgr_id = NULL WHERE mgr_id = ?";
            try (PreparedStatement updatePstmt = conn.prepareStatement(updateSql)) {
                updatePstmt.setInt(1, empId);
                updatePstmt.executeUpdate();
            }

            // 然后删除该员工
            String deleteSql = "DELETE FROM emp WHERE emp_id = ?";
            try (PreparedStatement deletePstmt = conn.prepareStatement(deleteSql)) {
                deletePstmt.setInt(1, empId);
                deletePstmt.executeUpdate();
            }

            conn.commit(); // 提交事务
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("删除员工失败", e);
        } finally {
            DBUtil.closeConn(conn); // 关闭连接
        }
    }
}